package cn.pengpeng.hive.newUser;
/**
 * 
新增用户统计思路：
1、抽取出当日的活跃用户信息 etl_user_active_day
2、将当日活跃用户 跟 历史用户表  比对，得出当日的新用户信息表
3、将当日新用户插入历史表
4、对当日新用户信息表进行各维度聚合



**** 创建一个历史用户表 ****
create table etl_history_user(uid string);



**** 创建一个每日新用户信息表 ****
create table etl_user_new_day(uid string
, commit_time string
,city string
,release_channel string
,app_ver_name string) 
partitioned by (day string);


**** 创建一个每日新用户维度聚合表 ****
create table dim_user_new(city string,release_channel string,app_ver_name string,newuser_cnt int)
partitioned by (day string,flag string);




**** 实现步骤 ****
// 2017-07-01  新用户信息抽取
// 比对出新用户信息，插入新用户信息表
insert into table etl_user_new_day partition(day='20170101')
select     
    a.uid 
    ,a.commit_time 
    ,a.city 
    ,a.release_channel 
    ,a.app_ver_name 
from 
etl_user_active_day a 
  left join 
etl_history_user b 
  on a.uid=b.uid
where b.uid is null and a.day='20170701';


// 2017-07-02 新用户信息抽取
// 比对出新用户信息，插入新用户信息表
insert into table etl_user_new_day partition(day='20170702')
select     
    a.uid 
    ,a.commit_time 
    ,a.city 
    ,a.release_channel 
    ,a.app_ver_name 
from 
etl_user_active_day a 
  left join 
etl_history_user b 
  on a.uid=b.uid
where b.uid is null and a.day='20170702';

// 将新用户uid追加到历史表
insert into table etl_history_user
select uid from etl_user_new_day where day='20170701';


insert into table etl_history_user
select uid from etl_user_new_day where day='20170702';

// 对新用户信息表进行维度聚合
// 2017-07-01 的新用户数维度聚合查询

from etl_user_new_day
 
insert into table dim_user_new partition(day='20170701',flag='000')
select 'all','all','all',count(1) 
where day='20170701'

insert into table dim_user_new partition(day='20170701',flag='001')
select 'all','all',app_ver_name,count(1) 
where day='20170701'
group by app_ver_name

insert into table dim_user_new partition(day='20170701',flag='010')
select 'all',release_channel,'all',count(1) 
where day='20170701'
group by release_channel

insert into table dim_user_new partition(day='20170701',flag='011')
select 'all',release_channel,app_ver_name,count(1) 
where day='20170701'
group by release_channel,app_ver_name

insert into table dim_user_new partition(day='20170701',flag='100')
select city,'all','all',count(1) 
where day='20170701'
group by city


insert into table dim_user_new partition(day='20170701',flag='101')
select city,'all',app_ver_name,count(1) 
where day='20170701'
group by city,app_ver_name

insert into table dim_user_new partition(day='20170701',flag='110')
select city,release_channel,'all',count(1) 
where day='20170701'
group by city,release_channel


insert into table dim_user_new partition(day='20170701',flag='111')
select city,release_channel,app_ver_name,count(1) 
where day='20170701'
group by city,release_channel,app_ver_name;





// 2017-07-02 的新用户数维度聚合查询


from etl_user_new_day
 
insert into table dim_user_new partition(day='20170702',flag='000')
select 'all','all','all',count(1) 
where day='20170702'

insert into table dim_user_new partition(day='20170702',flag='001')
select 'all','all',app_ver_name,count(1) 
where day='20170702'
group by app_ver_name

insert into table dim_user_new partition(day='20170702',flag='010')
select 'all',release_channel,'all',count(1) 
where day='20170702'
group by release_channel

insert into table dim_user_new partition(day='20170702',flag='011')
select 'all',release_channel,app_ver_name,count(1) 
where day='20170702'
group by release_channel,app_ver_name

insert into table dim_user_new partition(day='20170702',flag='100')
select city,'all','all',count(1) 
where day='20170702'
group by city


insert into table dim_user_new partition(day='20170702',flag='101')
select city,'all',app_ver_name,count(1) 
where day='20170702'
group by city,app_ver_name

insert into table dim_user_new partition(day='20170702',flag='110')
select city,release_channel,'all',count(1) 
where day='20170702'
group by city,release_channel


insert into table dim_user_new partition(day='20170702',flag='111')
select city,release_channel,app_ver_name,count(1) 
where day='20170702'
group by city,release_channel,app_ver_name;















 */
public class NewUser {

}
